CREATE PROCEDURE [dbo].[asi_GetPublishRequests]
    @PublishServerCode nchar(1) AS
BEGIN
    SELECT PublishRequestKey FROM PublishRequest
    WHERE Exists (SELECT 1 FROM PublishRequestDetail prd, DocumentMain dm
                          WHERE prd.PublishRequestKey = PublishRequest.PublishRequestKey
                            AND prd.PublishServerCode = @PublishServerCode
                            AND prd.ContentDocumentKey = dm.DocumentKey
                            AND ((dm.DocumentStatusCode IN (30,40) AND prd.IsDelete = 0)
                                    OR (dm.DocumentStatusCode IN (70,80) AND prd.IsDelete = 1))
                           AND ((prd.PublishRequestStatusCode = 0 AND (prd.PublishedDateTime IS NULL OR prd.PublishedDateTime <= GetDate()))
                                OR
                                (PublishRequestStatusCode = 1 AND prd.IsDelete = 0 AND
                                (DATEADD(second, prd.Frequency, prd.PublishedDateTime) <= GetDate())
                                   AND (prd.Frequency <> 0))
                                )
                   )
ORDER BY PublishPriorityCode, RequestDateTime
END

GO